﻿Imports Business
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop

Public Class frmAttendance

#Region "Properties"

    Dim bsWorkData As BSWorkData

    Dim checkBox As CheckBox

    Dim dtEmployee As Data.DataTable

    Dim bsOrganization As BSOrganization

    Dim bsHoliday As BSHoliday

    Dim dtOrgan As Data.DataTable

    Dim years As Object() = New Object() {"2012", "2013", "2014", "2015", "2016", "2017", "2018", "2019", "2020"}

    Dim months As Object() = New Object() {"1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12"}

    ' start date of month
    Dim startDate As DateTime

    'end date of month
    Dim endDate As DateTime

    ' checkbox header gridview
    Dim chkBox As System.Windows.Forms.CheckBox

    ' string name columns check box select all
    Dim strColSelectAll As String = "Select All"

    Dim msg As String = "Working...."

    Dim xlApp As Excel.Application

    Dim xlBook As Excel.Workbook

    Dim xlSheet As Excel.Worksheet

    Dim strTitleExcel As String = "MAN-DAY MANAGEMENT LIST "

    Dim strColNoExcel As String = "NO."

    Dim strColEmpNoExcel As String = "EMPLOYEE NO"

    Dim strColEmpNameExcel As String = "NAME"

    Dim strColSumOfWorkingTimeExcel As String = "WORKING TIME"

    Dim strColSumOfWorkingDayExcel As String = "WORKING DAYS"

    Dim strColSumOfOTExcel As String = "OVERTIME"

    Dim bsFKAttend As BSFKAttend

    Dim serverName As String

#End Region

    Public Sub New(ByVal serverName As String)

        ' This call is required by the designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.
        Me.serverName = serverName

        bsWorkData = New BSWorkData(serverName)

        cboYear.Items.AddRange(years)

        cboMonth.Items.AddRange(months)

        cboYear.SelectedIndex = cboYear.Items.IndexOf(Date.Now.Year.ToString())

        cboMonth.SelectedIndex = cboMonth.Items.IndexOf(Date.Now.Month.ToString())

        'For i As Integer = 1 To Date.DaysInMonth(Date.Now.Year, Date.Now.Month)
        '    cboDay.Items.Add(i)
        'Next

        cboDay.SelectedIndex = cboDay.Items.IndexOf(Date.Now.Day)

        bsOrganization = New BSOrganization(serverName)

        bsHoliday = New BSHoliday(serverName)

        bsFKAttend = New BSFKAttend(serverName)

        ' get datatable all organization
        dtOrgan = bsOrganization.GetOrganization()

        frmCompanyDepartment.LoadTreeOrgan(dtOrgan, trvOrgan)

        trvOrgan.ExpandAll()

        dtEmployee = New Data.DataTable()



    End Sub


    Private Sub trvOrgan_AfterSelect(ByVal sender As System.Object, ByVal e As System.Windows.Forms.TreeViewEventArgs) Handles trvOrgan.AfterSelect

        ' load data gridview
        LoadDataGridView(e.Node, "%")

        ' set name organization 
        txtOrgan.Text = e.Node.Text

        If Not grdAttendance.Columns.Item(0).Name.Equals(strColSelectAll) Then

            'add column select all
            Dim colCheckbox As DataGridViewCheckBoxColumn = New DataGridViewCheckBoxColumn()

            colCheckbox.Name = strColSelectAll

            colCheckbox.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleLeft

            Me.grdAttendance.Columns.Insert(0, colCheckbox)

            chkBox = New System.Windows.Forms.CheckBox()

            '  Dim rect As Rectangle = Me.dgvEmployees.GetCellDisplayRectangle(0, -1, True)
            chkBox.Size = New Size(18, 18)

            chkBox.Location = New System.Drawing.Point(94, 2)

            AddHandler chkBox.CheckedChanged, AddressOf chkBox_CheckedChanged

            Me.grdAttendance.Controls.Add(chkBox)

        End If

        'set columns read only
        For i As Integer = 1 To grdAttendance.Columns.Count - 1

            grdAttendance.Columns.Item(i).ReadOnly = True

        Next

    End Sub


    ''' <summary>
    ''' get root node, get company
    ''' </summary>
    ''' <param name="node"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function FindRootNode(ByVal node As TreeNode) As TreeNode

        While Not node.Parent Is Nothing

            node = node.Parent

        End While

        Return node

    End Function

    ''' <summary>
    ''' Load datagridview employee List
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub LoadDataGridView(ByVal node As TreeNode, ByVal strSearch As String)

        bsWorkData.GetStartEndDateOfMonth(FindRootNode(node).Name, cboMonth.Text, cboYear.Text, startDate, endDate)

        '  lblMessage.Text = startDate.ToString("dd/MMM/yyyy") & "-->" & endDate.ToString("dd/MMM/yyyy")

        'get employees of node is selected
        Dim dsEmployee As DataSet = New DataSet()

        'get employee node root
        dsEmployee.Tables.Add(bsWorkData.GetAllEmployeeWorkData(node.Name, strSearch, startDate.ToString("yyyyMMdd"), endDate.ToString("yyyyMMdd")))

        ' get employee nodes children
        GetEmpChildNode(node.Name, dsEmployee, startDate, endDate, strSearch)

        ' merge all tables in dataset to one table
        dtEmployee = dsEmployee.Tables.Item(0).Copy()

        For i As Integer = 1 To dsEmployee.Tables.Count - 1

            dtEmployee.Merge(dsEmployee.Tables.Item(i))

        Next

        dtEmployee = bsWorkData.GetDataSourceEmpWork(dtEmployee)

        grdAttendance.DataSource = dtEmployee

        grdAttendance.ClearSelection()

        'set invisible 
        grdAttendance.Columns.Item(bsWorkData.strColOrganNo).Visible = False
        grdAttendance.Columns.Item(bsWorkData.strColExFrom).Visible = False
        grdAttendance.Columns.Item(bsWorkData.strColExTo).Visible = False

        ' insert work date to database, try catch
        For Each row As DataRow In dtEmployee.Rows

            ' check period of day in month exist
            If Not bsWorkData.CheckWorkDateExist(row.Item(bsWorkData.strColOrganNo), row.Item(bsWorkData.strColNo), startDate, endDate) Then

                bsWorkData.InsertWorkDate(row.Item(bsWorkData.strColOrganNo), row.Item(bsWorkData.strColNo),
                                          row.Item(bsWorkData.strColExFrom), row.Item(bsWorkData.strColExTo), startDate, endDate)

            End If

        Next

    End Sub


    ''' <summary>
    ''' get employee children
    ''' </summary>
    ''' <param name="supNodeKey"></param>
    ''' <param name="dsEmployee"></param>
    ''' <param name="startDate"></param>
    ''' <param name="endDate"></param>
    ''' <param name="strSearch"></param>
    ''' <remarks></remarks>
    Private Sub GetEmpChildNode(ByVal supNodeKey As String, ByRef dsEmployee As DataSet, ByVal startDate As DateTime,
                                ByVal endDate As DateTime, ByVal strSearch As String)

        'Dim dtOrgan As DataTable = bsOrganization.GetOrganization()

        For Each row As DataRow In dtOrgan.Rows

            If row.Item("SUP_ORGANIZATIONAL_NO").ToString().Equals(supNodeKey) Then

                'add employee in child node to dataset
                dsEmployee.Tables.Add(bsWorkData.GetAllEmployeeWorkData(row.Item("ORGANIZATIONAL_NO"), strSearch, startDate.ToString("yyyyMMdd"), endDate.ToString("yyyyMMdd")))

                ' recur to get employee in child node
                GetEmpChildNode(row.Item("ORGANIZATIONAL_NO"), dsEmployee, startDate, endDate, strSearch)

            End If

        Next

    End Sub


    Private Sub cboMonth_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboMonth.SelectedIndexChanged

        If Not String.IsNullOrEmpty(cboYear.Text) Then

            LoadCboDay()

        End If


        If Not trvOrgan.SelectedNode Is Nothing Then

            LoadDataGridView(trvOrgan.SelectedNode, "%")

        End If


    End Sub



    Private Sub cboYear_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboYear.SelectedIndexChanged

        If Not String.IsNullOrEmpty(cboMonth.Text) Then

            LoadCboDay()

        End If

        If Not trvOrgan.SelectedNode Is Nothing Then

            LoadDataGridView(trvOrgan.SelectedNode, "%")

        End If

    End Sub


    Private Sub grdAttendance_CellDoubleClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles grdAttendance.CellDoubleClick

        '     Try

        ' MsgBox("You double clicked on Row:" & grdAttendance.Rows(e.RowIndex).Cells.Item(2).Value)
        Dim nodeRoot As TreeNode = FindRootNode(trvOrgan.SelectedNode)


        'frmMain.AddTabpage(New frmEmployeeAttendance(grdAttendance.Rows.Item(e.RowIndex).Cells.Item(bsWorkData.strColNo).Value, grdAttendance.Rows.Item(e.RowIndex).Cells.Item(bsWorkData.strColName).Value,
        '                                             grdAttendance.Rows.Item(e.RowIndex).Cells.Item(bsWorkData.strColOrganNo).Value,
        '                                             startDate, endDate, grdAttendance.Rows.Item(e.RowIndex).Cells.Item(bsWorkData.strColExFrom).Value,
        '                                             grdAttendance.Rows.Item(e.RowIndex).Cells.Item(bsWorkData.strColExTo).Value, cboMonth.Text, cboYear.Text, Me.serverName),
        '                                         grdAttendance.Rows.Item(e.RowIndex).Cells.Item(bsWorkData.strColName).Value,
        '                                         "tabEmpAttend" & grdAttendance.Rows.Item(e.RowIndex).Cells.Item(bsWorkData.strColNo).Value)

        frmMain.AddTabpage(New frmEmployeeAttendance(grdAttendance.Rows.Item(e.RowIndex).Cells.Item(bsWorkData.strColNo).Value, grdAttendance.Rows.Item(e.RowIndex).Cells.Item(bsWorkData.strColName).Value,
                                                    grdAttendance.Rows.Item(e.RowIndex).Cells.Item(bsWorkData.strColOrganNo).Value,
                                                     nodeRoot.Name,
                                                     startDate, endDate, grdAttendance.Rows.Item(e.RowIndex).Cells.Item(bsWorkData.strColExFrom).Value,
                                                     grdAttendance.Rows.Item(e.RowIndex).Cells.Item(bsWorkData.strColExTo).Value, cboMonth.Text, cboYear.Text, Me.serverName),
                                                 grdAttendance.Rows.Item(e.RowIndex).Cells.Item(bsWorkData.strColName).Value,
                                                 "tabEmpAttend" & grdAttendance.Rows.Item(e.RowIndex).Cells.Item(bsWorkData.strColExFrom).Value)

        'Catch ex As Exception

        '    Console.WriteLine(ex.Message)

        'End Try

    End Sub

    Private Sub frmAttendance_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


    End Sub

    Private Sub chkBox_CheckedChanged(ByVal sender As Object, ByVal e As EventArgs)

        For i As Integer = 0 To Me.grdAttendance.RowCount - 1

            Me.grdAttendance(0, i).Value = Me.chkBox.Checked

        Next

        Me.grdAttendance.EndEdit()

    End Sub


    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click

        'check node on treeview have to be selected
        If Not trvOrgan.SelectedNode Is Nothing Then

            If String.IsNullOrEmpty(txtSearch.Text) Then

                LoadDataGridView(trvOrgan.SelectedNode, "%")

            Else

                ' get string search, organ no, start date, end date
                LoadDataGridView(trvOrgan.SelectedNode, txtSearch.Text.Trim())

            End If

        End If

        ' bsWorkData.UpdateStartTimeToWorkData("7", 20130418, 0, 1830)


    End Sub

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub btnChkAttendance_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnChkAttendance.Click

        lblMessage.Text = msg

        System.Windows.Forms.Application.DoEvents()

        Dim nonExistDB As Boolean

        Dim shortageData As Boolean

        For i As Integer = 0 To grdAttendance.Rows.Count - 1

            shortageData = CheckShortageData(grdAttendance.Rows.Item(i).Cells.Item(bsWorkData.strColOrganNo).Value,
                                             grdAttendance.Rows.Item(i).Cells.Item(bsWorkData.strColNo).Value, nonExistDB)

            If nonExistDB Then

                grdAttendance.Rows.Item(i).DefaultCellStyle.BackColor = Color.LightGray

            ElseIf shortageData Then

                grdAttendance.Rows.Item(i).DefaultCellStyle.BackColor = Color.LightGreen

            Else

                grdAttendance.Rows.Item(i).DefaultCellStyle.BackColor = Color.White

            End If

        Next

        System.Windows.Forms.Application.DoEvents()

        lblMessage.Text = Nothing

    End Sub



    ''' <summary>
    ''' Check shortage data, return true if not enough
    ''' </summary>
    ''' <param name="employeeNo"></param>
    ''' <param name="month"></param>
    ''' <param name="year"></param>
    ''' <param name="nonExist"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function CheckShortageData(ByVal organNo As String, ByVal employeeNo As String, ByRef nonExist As Boolean) As Boolean

        Dim dFrom As Date = startDate

        Dim dTo As Date = endDate

        If dTo > Date.Now Then

            dTo = Date.Now

        End If

        'get work data of employee in a month
        Dim dtWorkData As Data.DataTable = bsWorkData.GetWorkData(organNo, employeeNo, dFrom.ToString("yyyyMMdd"), dTo.ToString("yyyyMMdd"))

        If dtWorkData.Rows.Count > 0 Then

            For Each row As DataRow In dtWorkData.Rows

                If Not bsHoliday.CheckHoliday(organNo, row.Item("WORK_DATE")) Then

                    If Not IsDBNull(row.Item("WORK_TYPE_NO")) Then

                        Dim workType As Integer = Integer.Parse(row.Item("WORK_TYPE_NO"))

                        If workType <> 2 And workType <> 10 And workType <> 5 And workType <> 7 And workType <> 6 Then

                            If IsDBNull(row.Item("UPDATE_START_TIME")) Or IsDBNull(row.Item("UPDATE_END_TIME")) Then

                                ' not enough start & end
                                Return True

                            End If

                        End If

                    Else

                        If IsDBNull(row.Item("UPDATE_START_TIME")) Or IsDBNull(row.Item("UPDATE_END_TIME")) Then

                            ' not enough data start & end
                            Return True

                        End If

                    End If

                End If

            Next

            ' exist data in database
            nonExist = False

            Return False

        Else

            ' non exist in data database
            nonExist = True

        End If


    End Function


    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub btnReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReport.Click

        ' check there is employee to be selected
        If CheckEmployeeSelected() Then

            Dim saveDialog As SaveFileDialog = New SaveFileDialog()

            saveDialog.Filter = "MS Excel files (*.xls)|*.xls"

            saveDialog.FileName = "Report.xls"

            ' show save dialog
            If saveDialog.ShowDialog() = DialogResult.OK Then

                lblMessage.Text = msg

                System.Windows.Forms.Application.DoEvents()

                xlApp = New Excel.Application

                xlBook = xlApp.Workbooks.Open(System.Windows.Forms.Application.StartupPath & "\template.xls")

                xlSheet = xlBook.ActiveSheet


                ' Title on file excel
                Dim title As String = strTitleExcel & cboMonth.Text & "/" & cboYear.Text

                SetRangeValue(xlSheet, "I", 2, title)

                SetRangeValue(xlSheet, "D", 4, title)


                ' set column header
                'column no
                SetRangeValue(xlSheet, "A", 4, strColNoExcel)

                'column employee no
                SetRangeValue(xlSheet, "B", 4, strColEmpNoExcel)

                ' column name
                SetRangeValue(xlSheet, "C", 4, strColEmpNameExcel)

                ' column employee no
                SetRangeValue(xlSheet, "BN", 4, strColEmpNoExcel)

                ' column sum of working time
                SetRangeValue(xlSheet, "BO", 4, strColSumOfWorkingTimeExcel)

                'column sum of working days
                SetRangeValue(xlSheet, "BP", 4, strColSumOfWorkingDayExcel)

                'column sum of overtime
                SetRangeValue(xlSheet, "BQ", 4, strColSumOfOTExcel)


                ' list day trong work day yyyyMMdd
                Dim arrDaysFrInt As ArrayList = New ArrayList()

                ' get list day
                Dim arrDays As ArrayList = GetListDay(startDate, endDate, arrDaysFrInt)

                ' write day title
                For i As Integer = 0 To arrDays.Count - 1

                    xlSheet.Cells(5, 4 + i * 2) = arrDays.Item(i)

                Next

                ' count, number of employees is selected
                Dim countEmployee As Integer = 0

                For i As Integer = 0 To grdAttendance.Rows.Count - 1

                    ' check row is selected
                    If grdAttendance.Rows.Item(i).Cells.Item(strColSelectAll).Value = True Then

                        countEmployee += 1

                        ' get employee No in row
                        Dim empNo As String = grdAttendance.Rows.Item(i).Cells.Item(bsWorkData.strColNo).Value

                        ' get employee name in row 
                        Dim empName As String = grdAttendance.Rows.Item(i).Cells.Item(bsWorkData.strColName).Value

                        ' get employee ex from
                        Dim exFrom As String = grdAttendance.Rows.Item(i).Cells.Item(bsWorkData.strColExFrom).Value

                        Dim exTo As String = grdAttendance.Rows.Item(i).Cells.Item(bsWorkData.strColExTo).Value

                        ' get datatable work data 
                        Dim dtWorkData As Data.DataTable =
                            bsWorkData.GetWorkData(grdAttendance.Rows.Item(i).Cells.Item(bsWorkData.strColOrganNo).Value, empNo,
                                                   startDate.ToString("yyyyMMdd"), endDate.ToString("yyyyMMdd"))

                        ' write to excel file
                        ExportToExcel(dtWorkData, countEmployee - 1, empName, empNo, exFrom, exTo, arrDaysFrInt,
                                      grdAttendance.Rows.Item(i).Cells.Item(bsWorkData.strColOrganNo).Value)

                    End If

                Next

                SetBorderRange(countEmployee)

                xlBook.SaveAs(saveDialog.FileName)

                xlBook.Close()

                xlApp.Quit()

                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet)

                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook)

                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)

                System.Windows.Forms.Application.DoEvents()

                lblMessage.Text = Nothing

                Process.Start(saveDialog.FileName)

            End If

        Else

            ' set message 
            lblMessage.Text = "No employee was selected."

        End If

    End Sub


    Private Function GetListDay(ByVal dayf As Date, ByVal dayt As Date, ByRef arrDaysFormatInt As ArrayList) As ArrayList

        ' array list contains number of days in period 1 month
        Dim arrList As ArrayList = New ArrayList()

        ' period of time work in one month
        If dayf.Month = dayt.Month Then

            For i As Integer = dayf.Day To dayt.Day

                arrList.Add(i)

                arrDaysFormatInt.Add(dayf.ToString("yyyy") & dayf.ToString("MM") & i.ToString("00"))

            Next

        Else

            Dim numOfdays As Integer = Date.DaysInMonth(dayf.Year, dayf.Month)

            ' get last day in this month
            Dim lastDate As Date = New Date(dayf.Year, dayf.Month, numOfdays)

            ' Add rows into datagridview which is inside fisrt half          
            For i As Integer = dayf.Day To lastDate.Day

                arrList.Add(i)

                arrDaysFormatInt.Add(dayf.ToString("yyyy") & dayf.ToString("MM") & i.ToString("00"))

            Next

            ' Get first day in the other month 
            Dim firstDate As Date = New Date(dayt.Year, dayt.Month, 1)

            ' Add row into datagriview which is inside the rest
            '   AddRowFromDB(firstDate, dayTo)

            For i As Integer = firstDate.Day To dayt.Day

                arrList.Add(i)

                arrDaysFormatInt.Add(dayt.ToString("yyyy") & dayt.ToString("MM") & i.ToString("00"))

            Next

        End If

        Return arrList

    End Function


    ''' <summary>
    ''' set range value
    ''' </summary>
    ''' <param name="tSheet"></param>
    ''' <param name="columnLabel"></param>
    ''' <param name="rowNum"></param>
    ''' <param name="tValue"></param>
    ''' <remarks></remarks>
    Private Sub SetRangeValue(ByVal tSheet As Object, ByVal columnLabel As String, ByVal rowNum As Integer, ByVal tValue As Object)

        Dim rangName As String = columnLabel & rowNum

        tSheet.Range(rangName).Value = tValue

    End Sub

    ''' <summary>
    ''' set border file excel  
    ''' </summary>
    ''' <param name="columnName1"></param>
    ''' <param name="columnName2"></param>
    ''' <param name="rowCount"></param>
    ''' <remarks></remarks>
    Private Sub SetBorderRange(ByVal rowCount As Integer)

        For Each cells As Range In xlSheet.Range("A6" & ": BQ" & (rowCount * 2 + 5))

            cells.BorderAround()

        Next

    End Sub


    
    ''' <summary>
    ''' Export to excel
    ''' </summary>
    ''' <param name="dt">datatable work data</param>
    ''' <param name="index">index employee</param>
    ''' <param name="empName"></param>
    ''' <param name="empNo"></param>
    ''' <param name="exFrom">employee ex from</param>
    ''' <param name="exTo">employee ex to</param>
    ''' <param name="arrDaysFrInt">list day format yyyyMMdd</param>
    ''' <param name="organNo">organ no</param>
    ''' <remarks></remarks>
    Private Sub ExportToExcel(ByVal dt As Data.DataTable,
                              ByVal index As Integer,
                              ByVal empName As String,
                              ByVal empNo As String,
                              ByVal exFrom As String,
                              ByVal exTo As String,
                              ByVal arrDaysFrInt As ArrayList,
                              ByVal organNo As String)

        Dim flag As Boolean

        ' sum of working time
        Dim sumOfWorking As Double = 0

        ' sum of working day
        Dim sumOfWorkingDay As Double = 0

        ' sum of overtime
        Dim sumOfOt As Double = 0

        ' ghi vo file excel
        For i As Integer = 0 To arrDaysFrInt.Count - 1

            flag = False

            ' check holiday
            If bsHoliday.CheckHoliday(organNo, arrDaysFrInt.Item(i)) Then

                xlSheet.Range(xlSheet.Cells(6 + 2 * index, 4 + i * 2), xlSheet.Cells(6 + 2 * index, 5 + i * 2)).Interior.ColorIndex = 38
                xlSheet.Range(xlSheet.Cells(7 + 2 * index, 4 + i * 2), xlSheet.Cells(7 + 2 * index, 5 + i * 2)).Interior.ColorIndex = 38

            End If

            For j As Integer = 0 To dt.Rows.Count - 1

                If dt.Rows.Item(j).Item("WORK_DATE") = arrDaysFrInt.Item(i) Then

                   
                    ' set number format hour hh:mm
                    xlSheet.Range(xlSheet.Cells(6 + 2 * index, 4 + i * 2), xlSheet.Cells(6 + 2 * index, 5 + i * 2)).NumberFormat = "@"

                    If Not IsDBNull(dt.Rows.Item(j).Item("START_TIME")) Then

                        xlSheet.Cells(6 + 2 * index, 4 + i * 2) = Integer.Parse(dt.Rows.Item(j).Item("START_TIME")).ToString("00:##")

                        '' check midnight
                        'If CheckMidnight(dt.Rows.Item(j).Item("TIME_TABLE_NO"), midNight) Then

                        '    numOfMid += 1

                        'End If

                    End If

                    If Not IsDBNull(dt.Rows.Item(j).Item("END_TIME")) Then

                        xlSheet.Cells(6 + 2 * index, 5 + i * 2) = Integer.Parse(dt.Rows.Item(j).Item("END_TIME")).ToString("00:##")

                    End If

                    If Not IsDBNull(dt.Rows.Item(j).Item("WORKING_TIME")) Then

                        ' set working time
                        xlSheet.Cells(7 + 2 * index, 4 + i * 2) =
                            Double.Parse(dt.Rows.Item(j).Item("WORKING_TIME") / 60).ToString("##.##")

                        '  add sum of working time
                        sumOfWorking += Double.Parse(dt.Rows.Item(j).Item("WORKING_TIME") / 60)


                        ' get work contract 
                        Dim workContract As Double

                        bsWorkData.GetTimetableEmployee(empNo, exFrom, exTo, workContract)

                        ' add sum of working day
                        sumOfWorkingDay += Double.Parse(dt.Rows.Item(j).Item("WORKING_TIME")) / workContract


                    End If

                    If Not IsDBNull(dt.Rows.Item(j).Item("OVER_TIME")) Then

                        Dim overTime As Double = Double.Parse(dt.Rows.Item(j).Item("OVER_TIME")) / 60

                        ' set overtime
                        xlSheet.Cells(7 + 2 * index, 5 + i * 2) = overTime.ToString("##.##")

                        sumOfOt += overTime

                    End If

                    flag = True

                End If
            Next

            If Not flag Then

                xlSheet.Cells(6 + 2 * index, 4 + i * 2) = Nothing

                xlSheet.Cells(6 + 2 * index, 5 + i * 2) = Nothing

                xlSheet.Cells(7 + 2 * index, 4 + i * 2) = Nothing

                xlSheet.Cells(7 + 2 * index, 5 + i * 2) = Nothing

            End If

        Next

        ' set No.
        SetRangeValue(xlSheet, "A", 6 + 2 * index, index + 1)

        ' set employee no
        SetRangeValue(xlSheet, "B", 6 + 2 * index, empNo)

        ' set employee name
        SetRangeValue(xlSheet, "C", 6 + 2 * index, empName)

        ' set employee no
        SetRangeValue(xlSheet, "BN", 7 + 2 * index, empNo)

        ' set sum of working time
        SetRangeValue(xlSheet, "BO", 7 + 2 * index, sumOfWorking.ToString("##.##"))

        ' set sum of working day time
        SetRangeValue(xlSheet, "BP", 7 + 2 * index, RoundSumOfDayWork(sumOfWorkingDay).ToString("##.##"))

        ' set sum of overtime
        SetRangeValue(xlSheet, "BQ", 7 + 2 * index, sumOfOt.ToString("##.##"))



    End Sub



    ''' <summary>
    ''' function round sum of days working
    ''' </summary>
    ''' <param name="sum"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function RoundSumOfDayWork(ByVal sum As Double) As Double

        ' integer part
        Dim int As Integer = CInt(Math.Floor(sum))

        ' decimal
        Dim dec As Double = 0

        ' tach phan thap phan
        dec = sum - CInt(Math.Floor(sum))

        If dec < 0.35 Then

            Return int

        ElseIf dec >= 0.35 And dec < 0.75 Then

            Return int + 0.5

        Else

            Return int + 1

        End If

    End Function

    ''' <summary>
    ''' Load combobox day
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub LoadCboDay()

        cboDay.Items.Clear()

        For i As Integer = 1 To Date.DaysInMonth(Integer.Parse(cboYear.Text), Integer.Parse(cboMonth.Text))

            cboDay.Items.Add(i)

        Next

        cboDay.SelectedIndex = 0

    End Sub


    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub btnLoadAttendance_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoadAttendance.Click

        If Not trvOrgan.SelectedNode Is Nothing Then

            lblMessage.Text = msg
            System.Windows.Forms.Application.DoEvents()
           
            'Test get all log data of FP terminal attendance
            bsFKAttend.SaveLogData()

            lblMessage.Text = "Load success"
            System.Windows.Forms.Application.DoEvents()

        End If


    End Sub


    ''' <summary>
    ''' close tab
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click

        frmMain.RemoveTabpage()

    End Sub

    ''' <summary>
    ''' export excel working of situation employee selected in a particular day
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub btnWorkSituation_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnWorkSituation.Click

        ' check there is employee to be selected
        If CheckEmployeeSelected() Then

            Dim saveDialog As SaveFileDialog = New SaveFileDialog()

            saveDialog.Filter = "MS Excel files (*.xls)|*.xls"

            saveDialog.FileName = "AttendanceList.xls"

            ' show save dialog
            If saveDialog.ShowDialog() = DialogResult.OK Then

                lblMessage.Text = msg

                System.Windows.Forms.Application.DoEvents()

                xlApp = New Excel.Application

                xlBook = xlApp.Workbooks.Open(System.Windows.Forms.Application.StartupPath & "\TemplateAttendanceList.xls")

                xlSheet = xlBook.ActiveSheet


                ' Title on file excel
                Dim title As String = "Date:" & cboDay.Text & "/" & cboMonth.Text & "/" & cboYear.Text

                SetRangeValue(xlSheet, "A", 4, title)

                ' count, number of employees is selected
                Dim countEmployee As Integer = 0

                ' sum of presence
                Dim presence As Integer = 0

                ' sum of absence
                Dim absence As Integer = 0

                For i As Integer = 0 To grdAttendance.Rows.Count - 1

                    ' check row is selected
                    If grdAttendance.Rows.Item(i).Cells.Item(strColSelectAll).Value = True Then
                        
                        ' set organization
                        SetRangeValue(xlSheet, "A", 7 + countEmployee, grdAttendance.Rows.Item(i).Cells.Item(bsWorkData.strColOrganName).Value)

                        ' set employee no
                        SetRangeValue(xlSheet, "B", 7 + countEmployee, grdAttendance.Rows.Item(i).Cells.Item(bsWorkData.strColNo).Value)

                        ' set employee name
                        SetRangeValue(xlSheet, "C", 7 + countEmployee, grdAttendance.Rows.Item(i).Cells.Item(bsWorkData.strColName).Value)

                        Dim day As String = cboYear.Text & Integer.Parse(cboMonth.Text).ToString("00") &
                                            Integer.Parse(cboDay.Text).ToString("00")
                        
                        ' check going to work
                        If bsWorkData.CheckWorkSituation(grdAttendance.Rows.Item(i).Cells.Item(bsWorkData.strColOrganNo).Value,
                                                        grdAttendance.Rows.Item(i).Cells.Item(bsWorkData.strColNo).Value,
                                                        day) Then
                            ' on duty
                            SetRangeValue(xlSheet, "D", 7 + countEmployee, "Presence")

                            presence += 1

                        Else

                            ' absence
                            SetRangeValue(xlSheet, "D", 7 + countEmployee, "Absence")

                            absence += 1

                        End If

                        countEmployee += 1

                    End If

                Next

                ' set sum of employee
                SetRangeValue(xlSheet, "C", 7 + countEmployee, "Total of employee")
                SetRangeValue(xlSheet, "D", 7 + countEmployee, countEmployee)

                'set bold
                SetBoldRange("C" & 7 + countEmployee, "C" & 7 + countEmployee)

                ' set going to work
                SetRangeValue(xlSheet, "C", 8 + countEmployee, "Going to work")
                SetRangeValue(xlSheet, "D", 8 + countEmployee, presence)

                'set bold 
                SetBoldRange("C" & 8 + countEmployee, "C" & 8 + countEmployee)

                ' set absence
                SetRangeValue(xlSheet, "C", 9 + countEmployee, "Absence")
                SetRangeValue(xlSheet, "D", 9 + countEmployee, absence)

                'set bold
                SetBoldRange("C" & 9 + countEmployee, "C" & 9 + countEmployee)

                'set border
                For Each cells As Range In xlSheet.Range("A7" & ": D" & countEmployee + 6)

                    cells.BorderAround()

                Next

                'set border
                For Each cells As Range In xlSheet.Range("C" & 7 + countEmployee & ": " & "D" & 9 + countEmployee)

                    cells.BorderAround()

                Next


                xlBook.SaveAs(saveDialog.FileName)

                xlBook.Close()

                xlApp.Quit()

                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet)

                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook)

                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)

                System.Windows.Forms.Application.DoEvents()

                lblMessage.Text = Nothing

                Process.Start(saveDialog.FileName)

            End If
            
        Else

            ' set message 
            lblMessage.Text = "No employee was selected."

        End If

    End Sub


    ''' <summary>
    ''' check employee(s) is selected in datagridview
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function CheckEmployeeSelected() As Boolean

        For i As Integer = 0 To grdAttendance.Rows.Count - 1

            ' check row is selected
            If grdAttendance.Rows.Item(i).Cells.Item(strColSelectAll).Value = True Then

                Return True

            End If

        Next

        Return False

    End Function

    ''' <summary>
    ''' set font bold for range
    ''' </summary>
    ''' <param name="startRange"></param>
    ''' <param name="endRange"></param>
    ''' <remarks></remarks>
    Private Sub SetBoldRange(ByVal startRange As String, ByVal endRange As String)

        For Each cells As Range In xlSheet.Range(startRange & ": " & endRange)

            cells.Cells.Font.Bold = True

        Next


    End Sub

End Class